/*
 * Copyright (c) 2011 CCXE(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCXE(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCXE(China) Co.,Ltd.
 *
 * Author: zhaoshujin
 * Date: 2012-2-23 下午07:26:29
 */
package com.jxl.excelplugin;

import java.io.File;

import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 合并金融类指标和非金融类指标，求出并集
 * 
 * @author zhaoshujin
 * 
 */
public class CollatingReport {

	public static String sourceFile = "F:\\MYDOCUMENT\\ExcelPlugin\\_temp\\财务报表分类信息.xls";
	public static String targetFile = "F:\\MYDOCUMENT\\ExcelPlugin\\_temp\\财务报表分类信息_并集.xls";

	public void collating() {

		try {
			Workbook wb = Workbook.getWorkbook(new File(sourceFile));
			WritableWorkbook wwb = null;

			// 如果文件已存在，打开修改；否则新建
			if (new File(targetFile).exists()) {
				Workbook wbb = Workbook.getWorkbook(new File(targetFile));
				wwb = Workbook.createWorkbook(new File(targetFile), wbb);
			} else
				wwb = Workbook.createWorkbook(new File(targetFile));

			// 获取新准则SHEET对象
			Sheet newNonFinancial = wb.getSheet("新准则-非金融企业");
			Sheet newFinancialInsurance = wb.getSheet("新准则-保险公司");
			Sheet newFinancialSecurities = wb.getSheet("新准则-证券公司");
			Sheet newFinancialBusiness = wb.getSheet("新准则-商业银行");

			// 获取旧准则SHEET对象
			Sheet oldFinancial = wb.getSheet("旧准则-金融类企业");
			Sheet oldNonFinancial = wb.getSheet("旧准则-非金融企业");

			// 新准则-利润表
			collatingUnion("新准则-利润表", newNonFinancial, wwb, 3, 45);
			collatingUnion("新准则-利润表", newFinancialInsurance, wwb, 3, 62);
			collatingUnion("新准则-利润表", newFinancialSecurities, wwb, 3, 48);
			collatingUnion("新准则-利润表", newFinancialBusiness, wwb, 3, 48);

			// 新准则-资产负债表
			collatingUnion("新准则-资产负债表", newNonFinancial, wwb, 50, 146);
			collatingUnion("新准则-资产负债表", newFinancialInsurance, wwb, 67, 165);
			collatingUnion("新准则-资产负债表", newFinancialSecurities, wwb, 53, 131);
			collatingUnion("新准则-资产负债表", newFinancialBusiness, wwb, 53, 127);

			// 新准则-现金流量表
			collatingUnion("新准则-现金流量表", newNonFinancial, wwb, 151, 247);
			collatingUnion("新准则-现金流量表", newFinancialInsurance, wwb, 170, 272);
			collatingUnion("新准则-现金流量表", newFinancialSecurities, wwb, 136, 236);
			collatingUnion("新准则-现金流量表", newFinancialBusiness, wwb, 132, 234);

			// 旧准则-利润表
			collatingUnion("旧准则-利润表", oldFinancial, wwb, 3, 65);
			collatingUnion("旧准则-利润表", oldNonFinancial, wwb, 3, 54);

			// 旧准则-资产负债表
			collatingUnion("旧准则-资产负债表", oldFinancial, wwb, 70, 231);
			collatingUnion("旧准则-资产负债表", oldNonFinancial, wwb, 59, 154);

			// 旧准则-现金流量表
			collatingUnion("旧准则-现金流量表", oldFinancial, wwb, 236, 420);
			collatingUnion("旧准则-现金流量表", oldNonFinancial, wwb, 159, 242);

			wwb.write();
			wwb.close();
			wb.close();

		} catch (Exception e) {
			e.printStackTrace();

		}
	}

	/**
	 * 将指定数据追加到SHEET里
	 * 
	 * @param reportName
	 *            已有数据的SHEET名称
	 * @param nextSheet
	 *            待添加数据的SHEET对象
	 * @param wwb
	 *            已有数据的EXCEL文档对象
	 * @param rowsOFStart
	 *            待添加数据的开始行号
	 * @param rowsOFEnd
	 *            待添加数据的结束行号
	 */
	public void collatingUnion(String reportName, Sheet nextSheet, WritableWorkbook wwb, int rowsOFStart, int rowsOFEnd) {

		try {

			// 新文档的SHEET数量
			int sheetNum = wwb.getNumberOfSheets();

			// 本次待添加的数据行数
			int rowsNext = rowsOFEnd - rowsOFStart + 1;

			String nameNext = null; // 待添加的指标名称
			String fieldNext = null; // 待添加指标对应的数据表字段

			String namePre = null; // 已有的指标名称
			String fieldPre = null; // 已有的查询字段

			// 将待添加数据放入二位数组
			String[][] arrayNext = new String[rowsNext][2];
			for (int r = rowsOFStart - 1, rr = 0; r < rowsOFEnd; r++) {

				nameNext = nextSheet.getCell(0, r).getContents().toLowerCase().trim();
				fieldNext = nextSheet.getCell(2, r).getContents().toLowerCase().trim();

				// 有指标名称并且有字段的行才算有效数据
				if (!nameNext.equals("") && !fieldNext.equals("")) {
					arrayNext[rr][0] = nameNext;
					arrayNext[rr][1] = fieldNext;
					rr++;
				}
			}

			// 如果指定的sheet已存在，则修改；否则新建
			WritableSheet ws = null;
			if (wwb.getSheet(reportName) != null)
				ws = wwb.getSheet(reportName);
			else
				ws = wwb.createSheet(reportName, sheetNum);

			// 设置列宽
			ws.setColumnView(0, 70);
			ws.setColumnView(1, 40);

			// 读取已存在的数据，存入二维数组中
			int rowsPre = ws.getRows();
			String[][] arrayPre = new String[rowsPre][2];
			for (int r = 0; r < rowsPre; r++) {

				namePre = ws.getCell(0, r).getContents().toLowerCase();
				fieldPre = ws.getCell(1, r).getContents().toLowerCase();
				arrayPre[r][0] = namePre;
				arrayPre[r][1] = fieldPre;
			}

			// 将待添加的数据，排除重复后，追加到新文档中
			for (int rr = 0; rr < arrayNext.length; rr++) {

				boolean isRepeat = false;
				nameNext = arrayNext[rr][0];
				fieldNext = arrayNext[rr][1];

				for (int r = 0; r < arrayPre.length; r++) {

					namePre = arrayPre[r][0];
					fieldPre = arrayPre[r][1];

					if (namePre.equals(nameNext) && fieldPre.equals(fieldNext)) {
						isRepeat = true;
						break;
					}
				}

				// 没有重复才追加
				if (!isRepeat) {
					int rowsWS = ws.getRows();

					// 名称和字段都有值才追加
					if (nameNext != null && !"".equals(nameNext) && fieldNext != null && !"".equals(fieldNext)) {
						ws.addCell(new Label(0, rowsWS, nameNext));
						ws.addCell(new Label(1, rowsWS, fieldNext));
					}
				}
			}

			System.out.println("sheet name:" + reportName);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {

		CollatingReport cr = new CollatingReport();
		long start = System.currentTimeMillis();

		cr.collating();

		long end = System.currentTimeMillis();
		System.out.println((end - start) + " ms");
	}

}
